package com.wuseguang.springcloud.service.impl;


import com.wuseguang.springcloud.Classcardservice.ClassCardFieldService;
import com.wuseguang.springcloud.Filter.RequestParameterWrapper;
import com.wuseguang.springcloud.dao.jdbc.AttendJdbc;
import com.wuseguang.springcloud.entity.AttendGroup;
import com.wuseguang.springcloud.entity.AttendShift;
import com.wuseguang.springcloud.service.AttendService;
import com.wuseguang.springcloud.service.QuartzClientService;
import com.wuseguang.springcloud.thread.SaveAttendThread;
import com.wuseguang.springcloud.util.*;
import com.wuseguang.springcloud.util.DateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.tomcat.util.http.fileupload.IOUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.*;

@Service
public class AttendServiceImpl implements AttendService {

    private Logger logger = LoggerFactory.getLogger(this.getClass());

    private static String squeka = "进班缺卡";

    private static String chidao = "进班迟到";

    private static String fqueka = "离班缺卡";

    private static String zaotui = "离班早退";

    private static String qingjia = "请假";

    private static String xiuxi = "休息";

    private static String zhengchang = "正常";

    private static String weidaka = "未打卡";

    private static String queqin = "缺勤";

    @Autowired
    private AttendJdbc attendJdbc;

    @Autowired
    private ClassCardFieldService service;


    @Autowired
    private SaveAttendThread saveAttendThread;

    @Autowired
    private QuartzClientService quartzClientService;


    private String flag = "1";

    /**
     * @描述 组合查询学生或教师考勤
     * @创建人 zm
     * @创建时间 2020/2/3
     */
    @Override
    public Map<String, Object> queryAttendAll(String attend_groupid, String school_id, String starttime, String endtime, String classids, String sid) {
        String sql = "SELECT shift_id,attend_group_type,opertime FROM attendance_group WHERE id=?";
        Map<String, Object> groupObjectMap = null;
        try {
            groupObjectMap = attendJdbc.queryMap(sql, attend_groupid);
        } catch (Exception e) {
            logger.info("queryAttendAll-->groupObjectMap,error={}", e);
        }

        if (groupObjectMap == null) {
            return null;
        }
        String shiftid = (String) groupObjectMap.get("shift_id");//班次id列
        int type = (int) groupObjectMap.get("attend_group_type");//0学生考勤、1教师考勤
        String opertime = (String) groupObjectMap.get("opertime");
        String stime = starttime;
        int state = 0;
        String gettime = DateUtil.gettime();
        if (DateUtil.getDay(gettime, endtime) > 0) {//查过当前时间，取当前时间
            endtime = gettime;
        }
        if (DateUtil.getDay(gettime, starttime) > 0) {//查过当前时间，取当前时间
            starttime = gettime;
        }
        if (DateUtil.getDay(starttime, opertime) > 0) {
            stime = DateUtil.getdaytime(opertime);
            state = 1;
        }
        if (StringUtils.isEmpty(starttime) || StringUtils.isEmpty(endtime)) {
            return null;
        }
        endtime = DateUtil.addDays(endtime, 1);
        //考勤类型:0学生考勤、1教师考勤
        String s = "";
        if (!StringUtils.isEmpty(classids)) {//按班级筛选
            s = " AND t.class_id in (" + classids + ") ";
        }
        if (!StringUtils.isEmpty(sid)) {
            s = "";
        }
        List<Map<String, Object>> userList = null; //所有用户
        if (type == 0) {
            String sql3 = "SELECT t.id tid,t.student_name name,s.name cname FROM attendance_group_user u,student t " +
                    "   LEFT JOIN classes s  ON t.class_id=s.id AND s.school_id=?  WHERE u.attend_group_id=? AND t.id=u.userid" + s;
            userList = attendJdbc.queryListMap(sql3, school_id, attend_groupid);
        } else {
            String sql3 = " SELECT o.*,p.cname FROM  " +
                    " (SELECT t.id tid,t.name FROM attendance_group_user u,teacher t WHERE u.attend_group_id=? AND t.id=u.userid) o  " +
                    " LEFT JOIN (SELECT GROUP_CONCAT(cname)  cname,tid FROM ( SELECT  a.id cid,a.name cname,a.class_teacher_id tid" +
                    " FROM grade g, classes a  WHERE g.school_id=?  AND g.school_id=a.school_id AND g.id=a.grade_id  " +
                    " UNION  SELECT a.id cid,a.name" +
                    " cname,t.id tid  " +
                    " FROM classes a ,course e,teacher t,teacher_role r,roles s,grade g WHERE g.school_id=?  AND  " +
                    " t.id=r.teacher_id AND e.id=s.course_id AND s.id=r.role_id AND g.id=a.grade_id  AND a.id=r.pid " +
                    " AND s.type=1 AND r.type=3 GROUP BY cid) b GROUP BY tid ) p ON p.tid=o.tid WHERE 1=1 " + s;
            userList = attendJdbc.queryListMap(sql3, attend_groupid, school_id, school_id);
        }

        String sql2 = "SELECT id,type,starttime,endtime,starttime1,endtime1 FROM attendance_shift WHERE school_id=" + school_id;
        Map<String, Map<String, String>> mapShift = attendJdbc.getMapShift(sql2);
        return attendList(shiftid, userList, mapShift, stime, endtime, attend_groupid, school_id, state, starttime);
    }


    public Map<String, Object> attendList(String shiftid, List<Map<String, Object>> userList, Map<String, Map<String, String>> mapShift, String time, String endtime, String attend_groupid, String school_id, int state, String starttime) {
        Map<String, Object> maps = new HashMap<String, Object>();
        List<Map<String, Object>> mapList = new ArrayList<Map<String, Object>>();
        List<String> weekList = new ArrayList<String>();
        long wdays = DateUtil.getDay(starttime, endtime);
        weekList.add("姓名");
        weekList.add("部门");
        // weekList.add("考勤天数");
        //weekList.add("休息天数");
        weekList.add("正常次数");
        weekList.add("缺勤次数");
        weekList.add("缺卡次数");
        weekList.add("迟到次数");
        weekList.add("早退次数");
        for (int i = 0; i < wdays; i++) {
            String s2 = DateUtil.addDays(starttime, i);
            weekList.add(new StringBuilder(s2).append("(").append(DateUtil.getWeeks(s2)).append(")").toString());
        }
        if (userList == null || StringUtils.isEmpty(shiftid) || mapShift == null) {
            return null;
        }
        String[] split = shiftid.split(",");//格式 [1,2,3,4,5,0,0]
        long day = DateUtil.getDay(time, endtime);
        for (int i = 0; i < userList.size(); i++) {
            Map<String, Object> stringObjectHashMap = new HashMap<String, Object>();
            Map<String, Object> stringObjectMap = userList.get(i);
            int tid = (int) stringObjectMap.get("tid");   //教师id 或 学生id
            String name = (String) stringObjectMap.get("name");  //名字
            String cname = (String) stringObjectMap.get("cname"); //所在班级
            int a = 0;//"缺卡"
            int b = 0;//"进班迟到"
            int d = 0;//"离班早退"
            int g = 0;//"缺勤"
            int h = 0;//休息天数
            int f = 0; //正常
            List<String> strings = new ArrayList<String>();
            if (state == 1) {
                logger.info("state == 1");
                long days = DateUtil.getDay(starttime, time);
                if (DateUtil.getDay(endtime, time) > 0) {
                    days = wdays;
                }
                String sql = "SELECT s.attendtype,s.attendname FROM   attend_his s WHERE s.userid=? AND s.attend_group_id=? AND TO_DAYS(s.opertime)=TO_DAYS(?) LIMIT 1";
                for (int j = 0; j < days; j++) {
                    String s2 = DateUtil.addDays(starttime, j);
                    Map<String, Object> userAttendList = null; //所有用户
                    try {
                        logger.info("tid={}, attend_groupid={}, s2={}", tid, attend_groupid, s2);
                        userAttendList = attendJdbc.queryMap(sql, tid, attend_groupid, s2);
                    } catch (Exception e) {
                        logger.info("attendList-->userAttendList,error={}", e);
                    }
                    if (userAttendList == null) {
                        strings.add("");
                    } else {
                        String attendtype = (String) userAttendList.get("attendtype");
                        String[] split1 = attendtype.split(",");
                        //考勤类型:
                        //0 病假 1 事假 2 第一段：进班正常 3 第一段：进班迟到 4 第一段：进班缺卡 5 第一段：离班早退 6 第一段：离班正常 7 第一段：离班缺卡
                        //8 第二段：进班正常 9 第二段：进班迟到 10 第二段：进班缺卡 11 第二段：离班早退 12 第二段：离班正常 13 第二段：离班缺卡 14 缺勤 15 休息
                        for (int k = 0; k < split1.length; k++) {
                            String s = split1[k];
                            logger.info("attendList-->split1={}", s);
                            if ("4".equals(s) || "7".equals(s) || "10".equals(s) || "13".equals(s)) {
                                a++;
                            }
                            if ("3".equals(s) || "9".equals(s)) {
                                b++;
                            }
                            if ("5".equals(s) || "11".equals(s)) {
                                d++;
                            }
                            if ("14".equals(s)) {
                                g++;
                            }
                            if ("2".equals(s) || "8".equals(s) || "6".equals(s) || "12".equals(s)) {
                                f++;
                            }
                           /* if ("15".equals(s)) {
                                h++;
                            }*/
                        }
                        strings.add((String) userAttendList.get("attendname"));
                    }
                }
            }
            logger.info("attendList-->strings={}", strings.toString());
            String sql = "SELECT  COUNT(1) count FROM  vacation WHERE student_id=? AND state=1 AND DATE(stime)<=DATE(?) AND DATE(etime)>= DATE(?)";
            String sql1 = "SELECT o.userid tid,o.attend_group_id,o.shift_id,o.attendtime,o.attendtype FROM attend o WHERE o.school_id=? AND o.userid=? " +
                    " AND o.attend_group_id=? AND o.state=0 AND TO_DAYS(o.attendtime)=TO_DAYS(?) ORDER BY attendtime ";
            for (int j = 0; j < day; j++) {
                String s2 = DateUtil.addDays(time, j);
                logger.info("考勤天数时间 attendtime={},day={}", s2, day);
                int weekInt = DateUtil.getWeekInt(s2);
                int i1 = Integer.parseInt(split[weekInt - 1]);
                if (i1 == 0) {//为0 时 休息
                    strings.add(xiuxi);
                    //h++;
                } else {
                    if (attendJdbc.queryCount(sql, tid, s2, s2) > 0) {//判断请假
                        strings.add(qingjia);
                    } else {
                        logger.info("mapShift,i1={}", i1);
                        Map<String, String> stringStringMap = mapShift.get(String.valueOf(i1));
                        List<Map<String, Object>> attendList = attendJdbc.queryAttendListMap(sql1, school_id, tid, attend_groupid, s2);

                        logger.info("AttendServiceImpl==>queryAttendAll,sql1={}" + sql1);
                        Map<String, String> stringStringMap1 = attendString(attendList, stringStringMap, s2);
                        String attend = stringStringMap1.get("attend");
                        g += Integer.parseInt(stringStringMap1.get("queqin"));
                        a += Integer.parseInt(stringStringMap1.get("squeka")) + Integer.parseInt(stringStringMap1.get("fqueka"));
                        b += Integer.parseInt(stringStringMap1.get("chidao"));
                        d += Integer.parseInt(stringStringMap1.get("zaotui"));
                        f += Integer.parseInt(stringStringMap1.get("zhengchang"));
                        strings.add(attend);
                    }
                }
            }
            stringObjectHashMap.put("name", name);
            stringObjectHashMap.put("cname", cname);
            //stringObjectHashMap.put("kqdays", DateUtil.getDay(starttime, endtime));
            //stringObjectHashMap.put("xxdays", h);
            /*stringObjectHashMap.put("zhengchang", f);
            stringObjectHashMap.put("qqdays", g);
            stringObjectHashMap.put("qkdays", a);
            stringObjectHashMap.put("cddays", b);
            stringObjectHashMap.put("ztdays", d);*/
            List<String> list = new ArrayList<String>();
            list.add(f + "");
            list.add(g + "");
            list.add(a + "");
            list.add(b + "");
            list.add(d + "");
            list.addAll(strings);
            stringObjectHashMap.put("attendlist", list);
            mapList.add(stringObjectHashMap);
        }
        maps.put("attendlist", mapList);
        maps.put("weeklist", weekList);
        return maps;
    }

    /**
     * @描述 对考勤做出判断  考勤类型: 0 病假 1事假 2 正常 3 进班迟到 4进班缺卡 5 离班缺卡 6 离班早退 7 缺勤 8 未打卡
     * @创建人 zm
     * @创建时间 2020/2/4
     */

    public Map<String, String> attendString(List<Map<String, Object>> attendList, Map<String, String> stringStringMap, String s2) {
        HashMap<String, String> map = new HashMap<>();
        String getdaytime = DateUtil.getdaytime(s2);
        String type = stringStringMap.get("type");//考勤类型:0 1天1次、1 1天2次
        String starttime = stringStringMap.get("starttime");
        starttime = DateUtil.AddTime(getdaytime, starttime);
        String endtime = stringStringMap.get("endtime");
        endtime = DateUtil.AddTime(getdaytime, endtime);
        String starttime1 = stringStringMap.get("starttime1");
        starttime1 = DateUtil.AddTime(getdaytime, starttime1);
        String endtime1 = stringStringMap.get("endtime1");
        endtime1 = DateUtil.AddTime(getdaytime, endtime1);
        logger.info("attendString==>starttime={},endtime={},starttime1={},endtime1={}", starttime, endtime, starttime1, endtime1);
        int a = 0;//"进班缺卡"
        int b = 0;//"进班迟到"
        int c = 0;//"离班缺卡"
        int d = 0;//"离班早退"
        int e = 0;//"进班正常"
        int f = 0;//"离班正常"
        int g = 0;//"缺勤"

        //第二段
        int A = 0;//"进班缺卡"
        int B = 0;//"进班迟到"
        int C = 0;//"离班缺卡"
        int D = 0;//"离班早退"
        int E = 0;//"进班正常
        int F = 0;//"离班正常
        if (attendList == null || attendList.size() <= 0) {//如果今天没有考勤视为 缺勤
            map.put("attend", queqin);
            int o = 1;
            if (DateUtil.getDay(s2, DateUtil.time()) == 0) {//按当前时间特殊处理
                map.put("attend", weidaka);
                if ("0".equals(type)) {
                    String s = "";
                    if (!StringUtils.isEmpty(endtime) && !StringUtils.isEmpty(starttime1)) {//设置了中间休息时段  取中间时间 区分进班迟到 和 离班早退
                        long l = DateUtil.getcompTime(starttime1, endtime);
                        s = DateUtil.addtimeMinutes(endtime, (int) l / 2);
                    } else {//未设置了中间休息时段  取中间时间 区分进班迟到 和 离班早退
                        long l = DateUtil.getcompTime(endtime1, starttime);
                        s = DateUtil.addtimeMinutes(starttime, (int) l / 2);
                    }
                    if (DateUtil.valueCompTime(DateUtil.time(), s) > 0) {
                        map.put("attend", "进班缺卡");
                        a++;
                        o = 0;
                    }
                } else {
                    //求第一段考勤的中间时间  用来区分 进班迟到和离班早退
                    long l = DateUtil.getcompTime(endtime, starttime);
                    String s = DateUtil.addtimeMinutes(starttime, (int) l / 2);
                    //求第一段考勤的结束时间和第二段开始时间的中间时间  用来区分 离班正常和第二段的进班正常
                    long l1 = DateUtil.getcompTime(starttime1, endtime);
                    String s1 = DateUtil.addtimeMinutes(endtime, (int) l1 / 2);
                    //求第二段考勤的中间时间  用来区分 进班迟到和离班早退
                    long l2 = DateUtil.getcompTime(endtime1, starttime1);
                    String s3 = DateUtil.addtimeMinutes(starttime1, (int) l2 / 2);
                    if (DateUtil.valueCompTime(DateUtil.time(), s) > 0) {
                        map.put("attend", "进班缺卡");
                        a = 1;
                        o = 0;
                    }

                    if (DateUtil.valueCompTime(DateUtil.time(), s1) > 0) {
                        map.put("attend", "进班缺卡 离班缺卡");
                        a = 1;
                        c = 1;
                        o = 0;
                    }

                    if (DateUtil.valueCompTime(DateUtil.time(), s3) > 0) {
                        map.put("attend", "进班缺卡 离班缺卡 进班缺卡");
                        a = 1;
                        c = 1;
                        A = 1;
                        o = 0;
                    }

                }
            }
            if (o == 1) {
                a = 0;
                c = 0;
                A = 0;
            }
            map.put("squeka", String.valueOf(a + A));
            map.put("chidao", String.valueOf(b + B));
            map.put("fqueka", String.valueOf(c + C));
            map.put("zaotui", String.valueOf(d + D));
            map.put("zhengchang", String.valueOf(e + f + E + F));
            map.put("queqin", String.valueOf(o));
            return map;
        } else {
            if ("0".equals(type)) {//一天一段考勤
                String s = "";
                if (!StringUtils.isEmpty(endtime) && !StringUtils.isEmpty(starttime1)) {//设置了中间休息时段  取中间时间 区分进班迟到 和 离班早退
                    long l = DateUtil.getcompTime(starttime1, endtime);
                    s = DateUtil.addtimeMinutes(endtime, (int) l / 2);
                } else {//未设置了中间休息时段  取中间时间 区分进班迟到 和 离班早退
                    long l = DateUtil.getcompTime(endtime1, starttime);
                    s = DateUtil.addtimeMinutes(starttime, (int) l / 2);
                }
                logger.info("attendString,s=={}", s);
                for (int i = 0; i < attendList.size(); i++) {//遍历这一天的考勤 进行判断
                    Map<String, Object> stringObjectMap = attendList.get(i);
                    String attendtime = (String) stringObjectMap.get("attendtime");
                    if (DateUtil.valueCompTime(starttime, attendtime) >= 0) {
                        e = 1;//"进班正常"
                    } else {
                        if (DateUtil.valueCompTime(s, attendtime) >= 0) {//"进班迟到"
                            if (e == 0) {
                                b = 1;
                            }
                        }
                    }

                    if (DateUtil.valueCompTime(s, attendtime) < 0 && DateUtil.valueCompTime(endtime1, attendtime) > 0) {//"离班早退"
                        if (f == 0) {
                            d = 1;
                        }
                    } else if (DateUtil.valueCompTime(endtime1, attendtime) <= 0) {//"离班正常"
                        if (d == 0) {
                            f = 1;
                        }
                    }

                    if (DateUtil.getDay(s2, DateUtil.gettime()) == 0 && DateUtil.valueCompTime(DateUtil.time(), s) <= 0 && d == 0 && f == 0) {//今天考勤时间特殊处理
                        d = -1;
                    }
                }

                if (e == 0 && b == 0) {
                    a = 1;//"进班缺卡"
                }
                if (d == 0 && f == 0 && DateUtil.getDay(s2, DateUtil.gettime()) != 0) {
                    c = 1;//"离班缺卡"
                }
            } else {//一天两段考勤
                //求第一段考勤的中间时间  用来区分 进班迟到和离班早退
                long l = DateUtil.getcompTime(endtime, starttime);
                String s = DateUtil.addtimeMinutes(starttime, (int) l / 2);
                //求第一段考勤的结束时间和第二段开始时间的中间时间  用来区分 离班正常和第二段的进班正常
                long l1 = DateUtil.getcompTime(starttime1, endtime);
                String s1 = DateUtil.addtimeMinutes(endtime, (int) l1 / 2);
                //求第二段考勤的中间时间  用来区分 进班迟到和离班早退
                long l2 = DateUtil.getcompTime(endtime1, starttime1);
                String s3 = DateUtil.addtimeMinutes(starttime1, (int) l2 / 2);
                for (int i = 0; i < attendList.size(); i++) {//遍历这一天的考勤 进行判断
                    Map<String, Object> stringObjectMap = attendList.get(i);
                    String attendtime = (String) stringObjectMap.get("attendtime");
                    //第一段进班
                    if (DateUtil.valueCompTime(starttime, attendtime) >= 0) {
                        e = 1;//"进班正常"
                    } else {
                        if (DateUtil.valueCompTime(s, attendtime) >= 0) {//"进班迟到"
                            if (e == 0) {
                                b = 1;
                            }
                        }
                    }

                    //第一段离班
                    if (DateUtil.valueCompTime(endtime, attendtime) > 0 && DateUtil.valueCompTime(s, attendtime) < 0) {//"离班早退"
                        if (f == 0) {
                            d = 1;
                        }
                    } else {
                        if (DateUtil.valueCompTime(s1, attendtime) >= 0 && DateUtil.valueCompTime(endtime, attendtime) <= 0) {//"离班正常
                            if (d == 0) {
                                f = 1;
                            }
                        }
                    }

                    if (DateUtil.getDay(s2, DateUtil.gettime()) == 0 && DateUtil.valueCompTime(DateUtil.time(), s1) <= 0 && d == 0 && f == 0) {//今天考勤时间特殊处理
                        d = -1;
                    }

                    //第二段进班
                    if (DateUtil.valueCompTime(starttime1, attendtime) >= 0 && DateUtil.valueCompTime(s1, attendtime) < 0) {
                        E = 1;//"进班正常"
                    } else {
                        if (DateUtil.valueCompTime(starttime1, attendtime) < 0 && DateUtil.valueCompTime(s3, attendtime) >= 0) {//"进班迟到"
                            if (E == 0 || E == -1) {
                                B = 1;
                            }
                        }
                    }

                    if (DateUtil.getDay(s2, DateUtil.gettime()) == 0 && DateUtil.valueCompTime(DateUtil.time(), s3) <= 0 && E == 0 && B == 0) {//今天考勤时间特殊处理
                        E = -1;
                    }

                    //第二段离班
                    if (DateUtil.valueCompTime(endtime1, attendtime) > 0 && DateUtil.valueCompTime(s3, attendtime) < 0) {//"离班早退"
                        D = 1;
                    } else {
                        if (DateUtil.valueCompTime(endtime1, attendtime) <= 0) {//"离班正常
                            if (D == 0) {
                                F = 1;
                            }
                        }
                    }
                }
                if (e == 0 && b == 0) {
                    a = 1;//"进班缺卡"
                }
                if (d == 0 && f == 0) {
                    c = 1;//"离班缺卡"
                }

                if (E == 0 && B == 0) {
                    A = 1;//"进班缺卡"
                }
                if (D == 0 && F == 0 && DateUtil.getDay(s2, DateUtil.gettime()) != 0) {
                    C = 1;//"离班缺卡"
                }
            }
        }
        if ("0".equals(type)) {
            if (c == 1 && a == 1) {
                g++;
            }
        } else {
            if (c == 1 && a == 1 && C == 1 && A == 1) {
                g++;
            }
        }
        if (E == -1) {
            E = 0;
        }
        if (d == -1) {
            d = 0;
        }
        map.put("attend", attendString(a, b, c, d, e, f, g, A, B, C, D, E, F, s2));
        map.put("squeka", String.valueOf(a + A));
        map.put("chidao", String.valueOf(b + B));
        map.put("fqueka", String.valueOf(c + C));
        map.put("zaotui", String.valueOf(d + D));
        map.put("zhengchang", String.valueOf(e + f + E + F));
        map.put("queqin", String.valueOf(g));
        logger.info("attendString={}", map.toString());
        return map;
    }


    public String attendString(int a, int b, int c, int d, int e, int f, int g, int A, int B, int C, int D, int E, int F, String s2) {
        logger.info("attendString-->,a={},b={},c={},d={},e={},f={},g={},A={},B={},C={},D={},E={},F={}", a, b, c, d, e, f, g, A, B, C, D, E, F);
        StringBuilder builder = new StringBuilder();
        if (g == 1) {
            builder.append("缺勤");
            return builder.toString();
        }
        if (e == 1) {
            builder.append("进班正常 ");
        }
        if (a == 1) {
            builder.append("进班缺卡 ");
        }
        if (b == 1) {
            builder.append("进班迟到 ");
        }
        if (f == 1) {
            builder.append("离班正常");
        }
        if (c == 1) {
            builder.append("离班缺卡");
        }
        if (d == 1) {
            builder.append("离班早退");
        }

        if (A == 1) {
            builder.append(" 进班缺卡");
        }
        if (B == 1) {
            builder.append(" 进班迟到");
        }
        if (E == 1) {
            builder.append(" 进班正常");
        }
        if (C == 1) {
            builder.append(" 离班缺卡");
        }
        if (D == 1) {
            builder.append(" 离班早退");
        }
        if (F == 1) {
            builder.append(" 离班正常");
        }
        return builder.toString();
    }

    /**
     * @描述 查该学校所有考勤组
     * @创建人 zm
     * @创建时间 2020/2/5
     */
    @Override
    public List<Map<String, Object>> queryAttendGroup(String school_id, String admin, String teacherId) {
        String sql = "SELECT id,name FROM attendance_group WHERE school_id=? ";
        if (!"0".equals(admin)) {
            String classIds = attendJdbc.findClassIds(school_id, teacherId);
            sql = " SELECT a.id,a.name  FROM attendance_group a,attendance_group_classid g WHERE a.id=g.attend_group_id AND a.school_id=? AND  g.class_id in (" + classIds + ")";
        }
        List<Map<String, Object>> mapList = attendJdbc.queryListMap(sql, school_id);
        return mapList;
    }


    /**
     * @描述 组合查询所有考勤组
     * @创建人 zm
     * @创建时间 2020/2/5
     */
    @Override
    public List<Map<String, Object>> queryAttendGroupAll(String school_id, String name) {
        StringBuilder stringBuilder = new StringBuilder(" SELECT g.id,g.name,(SELECT COUNT(1) FROM attendance_group_user a WHERE a.attend_group_id=g.id) count,g.attend_group_type attendtype,shift_id FROM attendance_group g WHERE g.school_id=? ");
        if (!StringUtils.isEmpty(name)) {
            stringBuilder.append("and g.name like ").append("'%").append(name).append("%'");
        }
        List<Map<String, Object>> mapList = attendJdbc.queryAttendGroupListMap(stringBuilder.toString(), school_id);
        return mapList;
    }

    /**
     * @描述 删除考勤组
     * @创建人 zm
     * @创建时间 2020/2/5
     */
    @Override
    @Transactional
    public int delAttendGroupById(String school_id, String id) {
        String s = attendGroupClassid(Integer.parseInt(id));
        String sql = " DELETE FROM attendance_group  WHERE id=? AND  school_id=?";
        int i = attendJdbc.updateByParameter(sql, id, school_id);
        if (i > 0) {
            String sql1 = " DELETE FROM attendance_group_classid   WHERE attend_group_id=? ";
            attendJdbc.updateByParameter(sql1, id);
            if (!StringUtils.isEmpty(s)) {
                service.pushClassCard(s, "dialogflag", flag);
            }
        }
        return i;
    }

    /**
     * @描述 根据id查询考勤组详细内容
     * @创建人 zm
     * @创建时间 2020/2/5
     */
    @Override
    public Map<String, Object> queryAttendGroupById(String school_id, String id) {
        String sql = "SELECT g.id,g.name,g.attend_group_type attendtype,shift_id FROM attendance_group g WHERE g.id=? AND g.school_id=?  ";
        Map<String, Object> map = null;
        try {
            map = attendJdbc.queryMap(sql, id, school_id);
        } catch (Exception e) {
            logger.info("queryAttendGroupById-->mapList,error={}", e);
        }
        if (map == null) {
            return null;
        }
        String sql2 = "SELECT GROUP_CONCAT(a.class_id) string FROM attendance_group_classid a,classes s WHERE a.attend_group_id=? AND a.class_id=s.id";
        map.put("classids", attendJdbc.queryString(sql2, id));
        String sql3 = "SELECT GROUP_CONCAT(u.userid) string FROM attendance_group_user u  WHERE u.attend_group_id=?";
        map.put("userids", attendJdbc.queryString(sql3, id));
        String shift_id = (String) map.get("shift_id");
        List<String> strings = new ArrayList<>();
        if (StringUtils.isEmpty(shift_id)) {
            map.put("shiftlist", strings);
            return map;
        }
        String[] split = shift_id.split(",");
        String sql1 = "SELECT type,starttime,endtime,starttime1,endtime1 FROM  attendance_shift WHERE id=?";
        for (int i = 0; i < split.length; i++) {
            String s1 = split[i];
            if ("0".equals(s1)) {
                strings.add("休息");
            } else {
                Map<String, Object> mapList = null;
                try {
                    mapList = attendJdbc.queryMap(sql1, s1);
                } catch (Exception e) {
                    logger.info("queryAttendGroupAll-->mapList,error={}", e);
                }
                if (mapList != null) {
                    Object starttime = mapList.get("starttime");
                    Object endtime = mapList.get("endtime");
                    Object starttime1 = mapList.get("starttime1");
                    Object endtime1 = mapList.get("endtime1");
                    StringBuilder stringBuilder = new StringBuilder();
                    Integer type = (Integer) mapList.get("type");
                    if (type == 0) {
                        stringBuilder.append(starttime).append("-").append(endtime1).append("  中午休息时间: ").append(endtime).append("-").append(starttime1);
                    } else {
                        stringBuilder.append(starttime).append("-").append(endtime).append(",").append(starttime1).append("-").append(endtime1);
                    }
                    strings.add(stringBuilder.toString());
                }
                map.put("shiftlist", strings);
            }
        }
        return map;
    }

    /**
     * @描述 添加考勤组
     * @创建人 zm
     * @创建时间 2020/2/5
     */
    @Override
    @Transactional
    public int addAttendGroup(String school_id, AttendGroup attendGroup) {
        String sql = "insert into `attendance_group` (`name`, `shift_id`, `attend_group_type`, `school_id`, `opertime`) values(?,?,?,?,now())";
        Integer aLong = attendJdbc.updateByParameterGetId(sql, attendGroup.getName(), attendGroup.getShift_id(), attendGroup.getAttend_group_type(), school_id).intValue();
        if (aLong > 0) {
            String classids = attendGroup.getClassids();
            updateAttendGroup(classids, aLong);
            String userids = attendGroup.getUserids();
            if (!StringUtils.isEmpty(userids)) {
                String sql3 = "INSERT INTO attendance_group_user(userid,attend_group_id,opertime) VALUES(?,?,NOW())";
                int i = attendJdbc.updateAttendGroupUser(sql3, userids, aLong);
                logger.info("addAttendGroup-->i={}", i);
            }
        }
        try {
            quartzClientService.saveAttend();
        } catch (Exception e) {

        }

        return aLong;
    }

    /**
     * @描述 修改考勤组
     * @创建人 zm
     * @创建时间 2020/2/5
     */
    @Override
    @Transactional
    public int updateAttendGroup(String school_id, AttendGroup attendGroup) {
        String sql4 = "update `attendance_group` set `name`=?, `shift_id`=?, `school_id`=?, `opertime`=now() where id=?";
        Integer attend_groupid = attendGroup.getId();
        String userids = attendGroup.getUserids();
        if (!StringUtils.isEmpty(userids) && attend_groupid != null) {
            String sql3 = "INSERT INTO attendance_group_user(userid,attend_group_id,opertime) VALUES(?,?,NOW())";
            int i = attendJdbc.updateAttendGroupUser(sql3, userids, attend_groupid);
            logger.info("updateAttendGroup-->i={}", i);
        }
        //把考勤attend 里面的数据添加到历史数据 atten_his里面
        String sql = "SELECT shift_id,attend_group_type,opertime FROM attendance_group WHERE id=?";
        Map<String, Object> groupObjectMap = null;
        try {
            groupObjectMap = attendJdbc.queryMap(sql, attendGroup.getId());
        } catch (Exception e) {
            logger.info("updateAttendGroup-->groupObjectMap,error={}", e);
        }
        if (groupObjectMap == null) {
            return attendJdbc.updateByParameter(sql4, attendGroup.getName(), attendGroup.getShift_id(), school_id, attendGroup.getId());
        }
        String shiftid = (String) groupObjectMap.get("shift_id");//班次id列
        int type = (int) groupObjectMap.get("attend_group_type");//0学生考勤、1教师考勤
        String opertime = (String) groupObjectMap.get("opertime");
        List<Map<String, Object>> userList = null; //所有用户
        if (type == 0) {
            String sql3 = " SELECT o.tid,o.NAME,p.cname FROM " +
                    "(SELECT t.id tid,t.student_name NAME,t.class_id  FROM attendance_group_user u,student t " +
                    " WHERE u.attend_group_id=? AND t.id=u.userid) o LEFT JOIN (SELECT a.name cname,a.id FROM grade g, classes a WHERE g.id=a.grade_id " +
                    " AND g.school_id=a.school_id AND a.school_id=? ) p ON o.class_id=p.id ";
            try {
                userList = attendJdbc.queryListMap(sql3, attend_groupid, school_id);
            } catch (Exception e) {
                logger.info("updateAttendGroup-->groupObjectMap,error={}", e);
            }
        } else {
            String sql3 = " SELECT o.*,p.cname FROM  " +
                    " (SELECT t.id tid,t.name FROM attendance_group_user u,teacher t WHERE u.attend_group_id=? AND t.id=u.userid) o  " +
                    " LEFT JOIN (SELECT GROUP_CONCAT(cname)  cname,tid FROM ( SELECT  a.id cid,a.name cname,a.class_teacher_id tid" +
                    " FROM grade g, classes a  WHERE g.school_id=?  AND g.school_id=a.school_id AND g.id=a.grade_id  " +
                    " UNION  SELECT a.id cid,a.name cname,t.id tid  " +
                    " FROM classes a ,course e,teacher t,teacher_role r,roles s,grade g WHERE g.school_id=?  AND  " +
                    " t.id=r.teacher_id AND e.id=s.course_id AND s.id=r.role_id AND g.id=a.grade_id  AND a.id=r.pid " +
                    " AND s.type=1 AND r.type=3 GROUP BY cid) b GROUP BY tid ) p ON p.tid=o.tid ";
            try {
                userList = attendJdbc.queryListMap(sql3, attend_groupid, school_id, school_id);
            } catch (Exception e) {
                logger.info("updateAttendGroup-->groupObjectMap,error={}", e);
            }
        }
        String sql2 = "SELECT id,TYPE,starttime,endtime,starttime1,endtime1 FROM attendance_shift WHERE school_id=" + school_id;
        Map<String, Map<String, String>> mapShift = attendJdbc.getMapShift(sql2);
        //updateattendList(shiftid, userList, mapShift, opertime, DateUtil.gettime(), attend_groupid + "", school_id, 0, opertime);
        saveAttendThread.setAttend_groupid(attend_groupid);
        saveAttendThread.setMapShift(mapShift);
        saveAttendThread.setOpertime(opertime);
        saveAttendThread.setShiftid(shiftid);
        saveAttendThread.setSchool_id(school_id);
        saveAttendThread.setUserList(userList);
        logger.info("开启保存考勤信息线程");
        new Thread(saveAttendThread).start();
        //添加完毕再删除 里面的数据
        //String sql1 = "UPDATE attend SET state=1 WHERE attendtime>=? and attendtime<?";
        //attendJdbc.updateByParameter(sql1, opertime, DateUtil.gettime());
        //最后在修改数据
        int i = attendJdbc.updateByParameter(sql4, attendGroup.getName(), attendGroup.getShift_id(), school_id, attendGroup.getId());
        if (i > 0) {
            Integer id = attendGroup.getId();
            String classids = attendGroup.getClassids();
            updateAttendGroup(classids, id);
        }
        return i;
    }

    @Transactional
    public void updateAttendGroup(String classids, Integer id) {
        if (!StringUtils.isEmpty(classids)) {
            //String s = attendGroupClassid(id);
            String sql1 = " DELETE FROM attendance_group_classid   WHERE attend_group_id=? ";
            attendJdbc.updateByParameter(sql1, id);
            String sql2 = " DELETE FROM attendance_group_classid   WHERE class_id in (" + classids + ") ";
            attendJdbc.updateByParameter(sql2);
            String listSql = "INSERT INTO attendance_group_classid(class_id,attend_group_id,opertime) VALUES(?,?,NOW())";
            int i = attendJdbc.updateAttendGroup(listSql, classids, id);
            if (i > 0) {
                /*if (!StringUtils.isEmpty(s)) {
                    service.classesAndCardsMultiple(s, "dialogflag", "2");
                }*/
                service.classesAndCardsMultiple(classids, "dialogflag", "2");
            }
        }
    }

    /**
     * @描述 根据考勤组查左右班级
     * @创建人 zm
     * @创建时间 2020/3/6
     */
    @Override
    public String attendGroupClassid(Integer id) {
        String sql2 = "SELECT GROUP_CONCAT(class_id) string FROM attendance_group_classid   WHERE attend_group_id=?";
        String sql = " UPDATE bancard SET dialogflag=0 WHERE classroomid IN ( SELECT id FROM classroom WHERE classid IN (SELECT class_id FROM attendance_group_classid   WHERE attend_group_id=?)) ";
        attendJdbc.updateByParameter(sql, id);
        return attendJdbc.queryString(sql2, id);
    }


    @Override
    public String updateattendList(String shiftid, List<Map<String, Object>> userList, Map<String, Map<String, String>> mapShift, String time, String endtime, String attend_groupid, String school_id, int state, String starttime) {
        String sql = "insert into `attend_his` (`userid`, `attend_group_id`, `attendname`, `attendtype`, `school_id`, `opertime`,`attendtime`,`shift_id`) values(?,?,?,?,?,?,?,?)";
        if (userList == null || StringUtils.isEmpty(shiftid) || mapShift == null) {
            return null;
        }
        String[] split = shiftid.split(",");//格式 [1,2,3,4,5,0,0]
        long day = DateUtil.getDay(time, endtime);
        for (int i = 0; i < userList.size(); i++) {
            Map<String, Object> stringObjectMap = userList.get(i);
            int tid = (int) stringObjectMap.get("tid");   //教师id 或 学生id
            //考勤类型:
            //0 病假 1 事假 2 第一段：进班正常 3 第一段：进班迟到 4 第一段：进班缺卡 5 第一段：离班早退 6 第一段：离班正常 7 第一段：离班缺卡
            //8 第二段：进班正常 9 第二段：进班迟到 10 第二段：进班缺卡 11 第二段：离班早退 12 第二段：离班正常 13 第二段：离班缺卡 14 缺勤 15 休息
            for (int j = 0; j < day; j++) {
                String s2 = DateUtil.addDays(time, j);
                logger.info("updateattendList,考勤天数时间 attendtime={}", s2);
                int weekInt = DateUtil.getWeekInt(s2);
                int i1 = Integer.parseInt(split[weekInt - 1]);
                String sql2 = "SELECT  COUNT(1) count FROM  vacation WHERE student_id=? AND state=1 AND DATE(stime)<=DATE(?) AND DATE(etime)>= DATE(?)";
                if (attendJdbc.queryCount(sql2, tid, s2, s2) > 0) {
                    attendJdbc.updateByParameter(sql, tid, attend_groupid, qingjia, 0, school_id, s2, "", i1);
                    continue;
                }
                if (i1 == 0) {//为0 时 休息
                    attendJdbc.updateByParameter(sql, tid, attend_groupid, xiuxi, 15, school_id, s2, "", i1);
                } else {
                    logger.info("updateattendList,mapShift,i1={}", i1);
                    Map<String, String> stringStringMap = mapShift.get(String.valueOf(i1));
                    List<Map<String, Object>> attendList = null;//查询对应考勤组里面的所有打卡时间
                    String sql1 = "SELECT o.userid tid,o.attend_group_id,o.shift_id,o.attendtime,o.attendtype FROM attend o WHERE o.school_id=? AND o.userid=? " +
                            " AND o.attend_group_id=? AND o.state=0 AND TO_DAYS(o.attendtime)=TO_DAYS(?) ORDER BY attendtime ";
                    try {
                        attendList = attendJdbc.queryAttendListMap(sql1, school_id, tid, attend_groupid, s2);
                    } catch (Exception ex) {
                        logger.info("updateattendList-->updateattendList,error={}", ex);
                    }
                    logger.info("AttendServiceImpl==>updateattendList,sql1={}" + sql1);
                    updateAttendString(attendList, stringStringMap, s2, attend_groupid, tid, school_id, sql, i1);
                }
            }
        }
        return null;
    }


    public int updateAttendString(List<Map<String, Object>> attendList, Map<String, String> stringStringMap, String s2, String attend_groupid, int tid, String school_id, String sql, int i1) {
        String getdaytime = DateUtil.getdaytime(s2);
        String type = stringStringMap.get("type");//考勤类型:0 1天1次、1 1天2次
        String starttime = stringStringMap.get("starttime");
        starttime = DateUtil.AddTime(getdaytime, starttime);
        String endtime = stringStringMap.get("endtime");
        endtime = DateUtil.AddTime(getdaytime, endtime);
        String starttime1 = stringStringMap.get("starttime1");
        starttime1 = DateUtil.AddTime(getdaytime, starttime1);
        String endtime1 = stringStringMap.get("endtime1");
        endtime1 = DateUtil.AddTime(getdaytime, endtime1);
        logger.info("attendString==>starttime={},endtime={},starttime1={},endtime1={}", starttime, endtime, starttime1, endtime1);
        int a = 0;//"进班缺卡"
        int b = 0;//"进班迟到"
        int c = 0;//"离班缺卡"
        int d = 0;//"离班早退"
        int e = 0;//"进班正常"
        int f = 0;//"离班正常"
        int g = 0;//"缺勤"

        //第二段
        int A = 0;//"进班缺卡"
        int B = 0;//"进班迟到"
        int C = 0;//"离班缺卡"
        int D = 0;//"离班早退"
        int E = 0;//"进班正常
        int F = 0;//"离班正常
        String time = "";
        if (attendList == null || attendList.size() <= 0) {//如果今天没有考勤视为 缺勤
            String s = "";
            if ("0".equals(type)) {
                s = "'',''";
            } else {
                s = "'','','',''";
            }
            attendJdbc.updateByParameter(sql, tid, attend_groupid, queqin, 14, school_id, s2, s, i1);
        } else {
            String time1 = "";
            String time2 = "";
            String time3 = "";
            if ("0".equals(type)) {//一天一段考勤
                String s = "";
                if (!StringUtils.isEmpty(endtime) && !StringUtils.isEmpty(starttime1)) {//设置了中间休息时段  取中间时间 区分进班迟到 和 离班早退
                    long l = DateUtil.getcompTime(starttime1, endtime);
                    s = DateUtil.addtimeMinutes(endtime, (int) l / 2);
                } else {//未设置了中间休息时段  取中间时间 区分进班迟到 和 离班早退
                    long l = DateUtil.getcompTime(endtime1, starttime);
                    s = DateUtil.addtimeMinutes(starttime, (int) l / 2);
                }
                logger.info("attendString,s=={}", s);
                for (int i = 0; i < attendList.size(); i++) {//遍历这一天的考勤 进行判断
                    Map<String, Object> stringObjectMap = attendList.get(i);
                    String attendtime = (String) stringObjectMap.get("attendtime");
                    if (DateUtil.valueCompTime(starttime, attendtime) >= 0) {
                        if (b == 0) {
                            e = 1;//"进班正常"
                            time = attendtime;
                        }
                    } else {
                        if (DateUtil.valueCompTime(s, attendtime) >= 0) {//"进班迟到"
                            if (e == 0) {
                                b = 1;
                                time = attendtime;
                            }
                        }
                    }

                    if (DateUtil.valueCompTime(s, attendtime) < 0 && DateUtil.valueCompTime(endtime1, attendtime) > 0) {//"离班早退"
                        if (f == 0) {
                            d = 1;
                            time1 += "," + attendtime;
                        }
                    } else if (DateUtil.valueCompTime(endtime1, attendtime) <= 0) {//"离班正常"
                        if (d == 0) {
                            f = 1;
                            time1 += "," + attendtime;
                        }
                    }
                }
                if (e == 0 && b == 0) {
                    a = 1;//"进班缺卡"
                    time += "''";
                }
                if (d == 0 && f == 0) {
                    c = 1;//"离班缺卡"
                    time += ",''";
                }
                time += time1;
            } else {//一天两段考勤
                //求第一段考勤的中间时间  用来区分 进班迟到和离班早退
                long l = DateUtil.getcompTime(endtime, starttime);
                String s = DateUtil.addtimeMinutes(starttime, (int) l / 2);
                //求第一段考勤的结束时间和第二段开始时间的中间时间  用来区分 离班正常和第二段的进班正常
                long l1 = DateUtil.getcompTime(starttime1, endtime);
                String s1 = DateUtil.addtimeMinutes(endtime, (int) l1 / 2);
                //求第二段考勤的中间时间  用来区分 进班迟到和离班早退
                long l2 = DateUtil.getcompTime(endtime1, starttime1);
                String s3 = DateUtil.addtimeMinutes(starttime1, (int) l2 / 2);
                for (int i = 0; i < attendList.size(); i++) {//遍历这一天的考勤 进行判断
                    Map<String, Object> stringObjectMap = attendList.get(i);
                    String attendtime = (String) stringObjectMap.get("attendtime");
                    //第一段进班
                    if (DateUtil.valueCompTime(starttime, attendtime) >= 0) {
                        e = 1;//"进班正常"
                        time = attendtime;
                    } else {
                        if (DateUtil.valueCompTime(s, attendtime) >= 0) {//"进班迟到"
                            if (e == 0) {
                                b = 1;
                                time = attendtime;
                            }
                        }
                    }

                    //第一段离班
                    if (DateUtil.valueCompTime(endtime, attendtime) > 0 && DateUtil.valueCompTime(s, attendtime) < 0) {//"离班早退"
                        if (f == 0) {
                            d = 1;
                            time1 += "," + attendtime;
                        }
                    } else {
                        if (DateUtil.valueCompTime(s1, attendtime) >= 0 && DateUtil.valueCompTime(endtime, attendtime) <= 0) {//"离班正常
                            if (d == 0) {
                                f = 1;
                                time1 += "," + attendtime;
                            }
                        }
                    }

                    //第二段进班
                    if (DateUtil.valueCompTime(starttime1, attendtime) >= 0 && DateUtil.valueCompTime(s1, attendtime) < 0) {
                        E = 1;//"进班正常"
                        time2 = "," + attendtime;
                    } else {
                        if (DateUtil.valueCompTime(starttime1, attendtime) < 0 && DateUtil.valueCompTime(s3, attendtime) >= 0) {//"进班迟到"
                            if (E == 0) {
                                B = 1;
                                time2 = "," + attendtime;
                            }
                        }
                    }

                    //第二段离班
                    if (DateUtil.valueCompTime(endtime1, attendtime) > 0 && DateUtil.valueCompTime(s3, attendtime) < 0) {//"离班早退"
                        D = 1;
                        time3 = "," + attendtime;
                    } else {
                        if (DateUtil.valueCompTime(endtime1, attendtime) <= 0) {//"离班正常
                            if (D == 0) {
                                F = 1;
                                time3 = "," + attendtime;
                            }
                        }
                    }
                }
                if (e == 0 && b == 0) {
                    time = "''";
                    a = 1;//"进班缺卡"
                }

                if (d == 0 && f == 0) {
                    c = 1;//"离班缺卡"
                    time1 = ",''";
                }

                if (E == 0 && B == 0) {
                    A = 1;//"进班缺卡"
                    time2 = ",''";
                }
                if (D == 0 && F == 0) {
                    C = 1;//"离班缺卡"
                    time3 = ",''";
                }
                time += time1 + time2 + time3;
            }
        }
        if ("0".equals(type)) {
            if (c == 1 && a == 1) {
                g++;
            }
        } else {
            if (c == 1 && a == 1 && C == 1 && A == 1) {
                g++;
            }
        }
        String s1 = attendString(a, b, c, d, e, f, g, A, B, C, D, E, F, s2);
        //考勤类型:
        //0 病假 1 事假 2 第一段：进班正常 3 第一段：进班迟到 4 第一段：进班缺卡 5 第一段：离班早退 6 第一段：离班正常 7 第一段：离班缺卡
        //8 第二段：进班正常 9 第二段：进班迟到 10 第二段：进班缺卡 11 第二段：离班早退 12 第二段：离班正常 13 第二段：离班缺卡 14 缺勤 15 休息
        String s = attendString1(a, b, c, d, e, f, g, A, B, C, D, E, F);
        if (s.indexOf(",") != -1) {
            s = s.substring(0, s.length() - 1);
        }
        logger.info("updateAttendString,updateByParameter={}", a, b, c, d, e, f, g, A, B, C, D, E, F);
        if (!"".equals("" + tid) && s1 != null && !"".equals(s1)) {
            attendJdbc.updateByParameter(sql, tid, attend_groupid, s1, s, school_id, s2, time, i1);
        }
        return 0;
    }

    //0 病假 1 事假 2 第一段：进班正常 3 第一段：进班迟到 4 第一段：进班缺卡 5 第一段：离班早退 6 第一段：离班正常 7 第一段：离班缺卡
    //8 第二段：进班正常 9 第二段：进班迟到 10 第二段：进班缺卡 11 第二段：离班早退 12 第二段：离班正常 13 第二段：离班缺卡 14 缺勤 15 休息
    public String attendString1(int a, int b, int c, int d, int e, int f, int g, int A, int B, int C, int D, int E, int F) {
        String s = "";
        if (g == 1) {
            s += "14";
            return s;
        }
        if (e == 1) {
            s += "2,";
        }

        if (b == 1) {
            s += "3,";
        }
        if (a == 1) {
            s += "4,";
        }
        if (d == 1) {
            s += "5,";
        }
        if (f == 1) {
            s += "6,";
        }

        if (c == 1) {
            s += "7,";
        }
        if (E == 1) {
            s += "8,";
        }

        if (B == 1) {
            s += "9,";
        }
        if (A == 1) {
            s += "10,";
        }

        if (D == 1) {
            s += "11,";
        }

        if (F == 1) {
            s += "12,";
        }
        if (C == 1) {
            s += "13,";
        }
        return s;
    }

    /**
     * @描述 查询所有班次
     * @创建人 zm
     * @创建时间 2020/2/5
     */
    @Override
    public List<Map<String, Object>> queryAttendShiftAll(String school_id) {
      /*  String sql = "SELECT  id,name,type,IF(TYPE=0, CONCAT(starttime,'-',endtime1,IF(endtime IS NULL,NULL,CONCAT('  中午休息时间: ',endtime,'-',starttime1))),CONCAT(starttime,'-',endtime,',',starttime1,'-',endtime1))  time , " +
                "  starttime,IF(TYPE=0,endtime1,endtime) endtime,IF(TYPE=0,endtime,starttime1) starttime1,IF(TYPE=0,starttime1,endtime1) endtime1,s_ejecttime,IF(TYPE=0,e_ejecttime1,e_ejecttime) e_ejecttime,s_ejecttime1,IF(TYPE=0,NULL,e_ejecttime1)  " +
                "  e_ejecttime1,school_id FROM attendance_shift WHERE school_id=? AND state=0";
*/
        String sql = "SELECT  id,name,type,IF(TYPE=0, CONCAT(if(starttime is null,'',starttime),'-',if(endtime1 is null,'',endtime1),IF(endtime IS NULL,'',CONCAT('  中午休息时间: ',endtime,'-',starttime1))),CONCAT(if(starttime is null,'',starttime),'-',if(endtime is null,'',endtime),',',if(starttime1 is null,'',starttime1),'-',if(endtime1 is null,'',endtime1)))  time , " +
                "  starttime,endtime,starttime1,endtime1,s_ejecttime,e_ejecttime,s_ejecttime1," +
                "  e_ejecttime1,school_id FROM attendance_shift WHERE school_id=? AND state=0";
        List<Map<String, Object>> mapList = null;
        try {
            mapList = attendJdbc.queryListMap(sql, school_id);
        } catch (Exception e) {
            logger.info("queryAttendGroupAll-->mapList,error={}", e);
        }
        return mapList;
    }

    /**
     * @描述 根据id 查班次详情
     * @创建人 zm
     * @创建时间 2020/2/5
     */
    @Override
    public Map<String, Object> queryAttendShiftById(String school_id, String id) {
        String sql = " SELECT  id,name,type,starttime,endtime,starttime1,endtime1,s_ejecttime,e_ejecttime,s_ejecttime1,e_ejecttime1,school_id FROM attendance_shift WHERE school_id= ? AND id=? and state=0";
        Map<String, Object> mapList = null;
        try {
            mapList = attendJdbc.queryMap(sql, school_id, id);
        } catch (Exception e) {
            logger.info("queryAttendGroupAll-->mapList,error={}", e);
        }
        return mapList;
    }

    /**
     * @描述 修改班次
     * @创建人 zm
     * @创建时间 2020/2/5
     */
    @Override
    public Result updateAttendShiftById(String school_id, AttendShift attendShift) {
        Integer id = attendShift.getId();
        Boolean verification = verification(school_id, id + "");
        if (verification) {
            return new Result(CodeUtil.CANNOT_UPDATE);
        }
        String sql = "update `attendance_shift` set `name`=?, `type`=?, `starttime`=?, `endtime`=?, `s_ejecttime`=?, `e_ejecttime`=?, `starttime1`=?, `endtime1`=?, `s_ejecttime1`=?, `e_ejecttime1`=?,  `addtime`=now() where id=?";
       /* if (attendShift.getType() == 0) {
            sql = "update `attendance_shift` set `name`=?, `type`=?, `starttime`=?, `endtime1`=?, `s_ejecttime`=?, `e_ejecttime1`=?,`endtime`=?,  `starttime1`=?, `s_ejecttime`=?, `e_ejecttime1`=?,  `addtime`=now() where id=?";
        }*/
        int i = attendJdbc.updateByParameter(sql, attendShift.getName(), attendShift.getType(), attendShift.getStarttime(), attendShift.getEndtime(),
                attendShift.getS_ejecttime(), attendShift.getE_ejecttime(), attendShift.getStarttime1(),
                attendShift.getEndtime1(), attendShift.getS_ejecttime1(), attendShift.getE_ejecttime1(), attendShift.getId());
        if (i > 0) {
            return new Result();
        }
        return new Result(CodeUtil.MODIFICATION_FAILED);
    }

    /**
     * @描述 添加班次
     * @创建人 zm
     * @创建时间 2020/2/5
     */
    @Override
    public Result addAttendShiftById(String school_id, AttendShift attendShift) {
        String sql = "SELECT count(1) ints FROM attendance_shift WHERE school_id=? AND name=? AND state=0";
        if (attendJdbc.queryInteger(sql, school_id, attendShift.getName()) > 0) {
            return new Result(CodeUtil.DUPLICATE_ACCOUNT_SHIFT);
        }
        sql = "insert into `attendance_shift` (`name`, `type`, `starttime`, `endtime`, `s_ejecttime`, `e_ejecttime`, `starttime1`, `endtime1`, `s_ejecttime1`, `e_ejecttime1`, `school_id`, `addtime`) values(?,?,?,?,?,?,?,?,?,?,?,now())";
        /*if (attendShift.getType() == 0) {
            sql = "insert into `attendance_shift` (`name`, `type`, `starttime`, `endtime1`, `s_ejecttime`, `e_ejecttime1`,  `endtime`,`starttime1`, `s_ejecttime1`, `e_ejecttime`, `school_id`, `addtime`) values(?,?,?,?,?,?,?,?,?,?,?,now())";
        }*/
        int i = attendJdbc.updateByParameter(sql, attendShift.getName(), attendShift.getType(), attendShift.getStarttime(), attendShift.getEndtime(),
                attendShift.getS_ejecttime(), attendShift.getE_ejecttime(), attendShift.getStarttime1(),
                attendShift.getEndtime1(), attendShift.getS_ejecttime1(), attendShift.getE_ejecttime1(), school_id);
        if (i > 0) {
            return new Result();
        }
        return new Result(CodeUtil.ADD_FAILURE);
    }


    public Boolean verification(String school_id, String id) {
        String sql1 = "SELECT id,shift_id FROM attendance_group WHERE school_id=? ";
        List<Map<String, Object>> mapList = null;
        try {
            mapList = attendJdbc.queryListMap(sql1, school_id);
        } catch (Exception e) {
            logger.info("delAttendShiftById-->mapList,error={}", e);
        }
        for (int i1 = 0; i1 < mapList.size(); i1++) {
            Map<String, Object> stringObjectMap = mapList.get(i1);
            String shift_id = (String) stringObjectMap.get("shift_id");
            if (StringUtils.isEmpty(shift_id)) {
                continue;
            }
            String[] split = shift_id.split(",");
            boolean contains = Arrays.asList(split).contains(id);
            if (contains) {
                return true;
            }
        }
        return false;
    }


    /**
     * @描述 删除班次
     * @创建人 zm
     * @创建时间 2020/2/5
     */
    @Override
    @Transactional
    public Result delAttendShiftById(String school_id, String id) {
        Boolean verification = verification(school_id, id);
        if (verification) {
            return new Result(CodeUtil.CANNOT_DELETE);
        }
        String sql = "update attendance_shift set state=1 WHERE school_id=? AND id=?";
        int i = attendJdbc.updateByParameter(sql, school_id, id);
        if (i > 0) {
            return new Result();
        }
        return new Result(CodeUtil.DELETE_FAILED);
    }

    @Override
    public Map<String, Object> downloadAttend(RequestParameterWrapper request, HttpServletResponse response, String sid, String attend_groupid, String starttime, String endtime, String classids) throws IOException {
        String schoolId = request.getParam("schoolId");
        logger.info("downloadAttend-->,attend_groupid={}, schoolId={}, starttime={}, endtime={}, userids={}, sid={}", attend_groupid, schoolId, starttime, endtime, classids, sid);
        return queryAttendAll(attend_groupid, schoolId, starttime, endtime, classids, sid);
    }

    @Override
    public Result findSchoolDepartmentStudent(String schoolId) {
        try {
            String sql = "select id,name from `grade` where `school_id`  = ?";
            logger.info("查询年级sql = {}", sql);
            List<Map<String, Object>> gradeList = attendJdbc.queryListMap(sql, Integer.parseInt(schoolId));
            for (Map<String, Object> gradeMap : gradeList) {
                logger.info("年级信息 map = {}", gradeMap.toString());
                sql = "select c.id,c.`name`  name  from `classes` c LEFT JOIN  `grade`  g on(g.id=c.`grade_id` ) where c.`grade_id` = ?";
                logger.info("查询班级信息 sql = {}", sql);
                List<Map<String, Object>> classList = attendJdbc.queryListMap(sql, gradeMap.get("id"));
                for (Map<String, Object> classMap : classList) {
                    sql = " SELECT id,student_name name FROM student WHERE class_id=? and state=0";
                    logger.info("查询学生信息 sql = {}", sql);
                    List<Map<String, Object>> studentList = attendJdbc.queryListMap(sql, classMap.get("id"));
                    classMap.put("list", studentList);
                }
                gradeMap.put("list", classList);
            }
            return new Result(gradeList);
        } catch (Exception e) {
            logger.info("findSchoolDepartmentStudent-->error={}", e);
        }
        return new Result();
    }

    @Override
    public Result queryAttendClassidState(String schoolId) {
        logger.info("查询学校的部门信息");
        //学生部门信息
        List<Map<String, Object>> gradeList = new ArrayList<Map<String, Object>>();
        try {
            String sql = "select id,name from `grade` where `school_id`  = ?";
            logger.info("查询年级sql = {}", sql);
            gradeList = attendJdbc.queryListMap(sql, Integer.parseInt(schoolId));
            for (Map<String, Object> gradeMap : gradeList) {
                sql = "SELECT c.id,c.`name`,IF(a.id IS NULL,0,1) state FROM  classes c LEFT JOIN attendance_group_classid a ON a.class_id=c.id  WHERE c.`grade_id` =? GROUP BY c.id ";
                List<Map<String, Object>> classList = attendJdbc.queryListMap(sql, gradeMap.get("id"));
                gradeMap.put("list", classList);
            }
        } catch (Exception e) {
            logger.info("queryAttendClassid-->,erorr={}", e);
        }
        return new Result(gradeList);
    }

    public void downexcle(HttpServletResponse response, List<Map<String, Object>> mapList,
                          List<String> weeklist, String filename)
            throws IOException {

        logger.info("downloadAttend_ExcelUtil downexcle.....");

        ByteArrayOutputStream os = new ByteArrayOutputStream();

        createWorkBook(mapList, weeklist, filename).write(os);

        logger.info("begin downloadExcel");

        downloadExcel(response, os, filename);

        logger.info("end downloadExcel");

    }

    public Workbook createWorkBook(List<Map<String, Object>> list, List<String> weeklist, String filename) {
        logger.info("createWorkBook(),list={}", list.toString());

        // 创建excel工作簿
        Workbook wb = new HSSFWorkbook();

        logger.info("sheetname=" + filename);

        // 创建第一个sheet（页），并命名
        Sheet sheet = wb.createSheet(filename);

        // 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。
        for (int i = 0; i < weeklist.size(); i++) {
            sheet.setColumnWidth((short) i, (short) (35.7 * 150));
        }

        // 创建两种单元格格式
        CellStyle cs = wb.createCellStyle();
        CellStyle cs2 = wb.createCellStyle();

        // 创建两种字体
        Font f = wb.createFont();
        Font f2 = wb.createFont();

        // 创建第一种字体样式（用于列名）
        f.setFontHeightInPoints((short) 12);
        f.setColor(IndexedColors.RED.getIndex());
        f.setFontName("宋体");

        // 创建第二种字体样式（用于值）
        f2.setFontHeightInPoints((short) 12);
        f2.setColor(IndexedColors.BLACK.getIndex());
        f2.setFontName("宋体");

        // 设置第一种单元格的样式（用于列名）
        cs.setFont(f);

        // 设置第二种单元格的样式（用于值）
        cs2.setFont(f2);


        logger.info("creating first row");
        // 创建第一行
        Row row = sheet.createRow((short) 0);
        row.setHeightInPoints(20);

        logger.info("create header weeklist={}", weeklist.toString());
        // 设置列名
        for (int i = 0; i < weeklist.size(); i++) {
            String s = weeklist.get(i);
            Cell cell = row.createCell(i);
            cell.setCellValue(s);
            cell.setCellStyle(cs);
            logger.info("set column={}, value={}", i, s);
        }


        // 设置每行每列的值
        if (!CollectionUtils.isEmpty(list)) {
            logger.info("list is not null,size=" + list.size());
            for (short i = 1; i <= list.size(); i++) {
                logger.info("create row = {}  / ", i, list.size());
                // Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
                // 创建一行，在页sheet上
                Row row1 = sheet.createRow((short) i);
                row1.setHeightInPoints(20);
                // 在row行上创建一个方格
                logger.info("create cell,weeklist.length=", weeklist.size());
                Map<String, Object> stringObjectMap = list.get(i - 1);
                String keys[] = {"name", "cname", "kqdays", "xxdays", "qqdays", "qkdays", "cddays", "ztdays"};
                int length = keys.length;
                for (short j = 0; j < length; j++) {
                    logger.info("create cell " + j);
                    Cell cell = row1.createCell(j);
                    // 判断是否为时间格式，如果为时间格式，要进行格式转换
                    String valueString = "";
                    Object bufferValue = stringObjectMap.get(keys[j]);
                    logger.info("cell[" + j + "]=" + bufferValue);
                    valueString = bufferValue == null ? "" : bufferValue.toString();
                    cell.setCellValue(valueString);
                    cell.setCellStyle(cs2);
                }
                List<String> attendlist = (List<String>) stringObjectMap.get("attendlist");
                for (int j = 0; j < attendlist.size(); j++) {
                    logger.info("create cell ", j + length);
                    Cell cell = row1.createCell(j + length);
                    // 判断是否为时间格式，如果为时间格式，要进行格式转换
                    String valueString = "";
                    Object bufferValue = attendlist.get(j);
                    logger.info("cell[" + j + "]=" + bufferValue);
                    valueString = bufferValue == null ? "" : bufferValue.toString();
                    cell.setCellValue(valueString);
                    cell.setCellStyle(cs2);
                }
                logger.info("write row finished,rowno=" + i);
            }
        } else {
            logger.info("list is null,not append data to excel");
        }

        logger.info(wb.toString());
        return wb;
    }


    public void downloadExcel(HttpServletResponse response, ByteArrayOutputStream os, String filename)
            throws IOException {

        logger.info("downloadExcel, filename=" + filename);

        byte[] content = os.toByteArray();
        InputStream is = new ByteArrayInputStream(content);

        // 设置response参数，可以打开下载页面
        response.reset();
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-Disposition",
                "attachment;filename=" + new String((filename + ".xls").getBytes(), "iso-8859-1"));
        /*response.setContentType("application/octet-stream");*/
        response.setHeader("Access-Control-Allow-Origin", "*");
        response.setHeader("Access-Control-Allow-Credentials", "true");
        response.setHeader("Access-Control-Allow-Methods", "GET,POST,PUT,DELETE,OPTIONS");
        ServletOutputStream out = response.getOutputStream();

        try {
            BufferedInputStream bis = new BufferedInputStream(is);
            BufferedOutputStream bos = new BufferedOutputStream(out);
            IOUtils.copy(bis, bos);
            bos.flush();

            //logger.info("content, byte size()=" +content.length+",content:" + new String(content, "utf-8"));

        } catch (Exception ex) {
            logger.error("export to excel failed,filename=" + filename, ex);
        }
    }

    /**
     * @描述 根据日期查该天课堂点名数据统计web
     * @创建人 zm
     * @创建时间 2020/5/18
     */
    @Override
    public Result queryClassCallList(Integer schoolId, Integer gradeId, Integer classId, String startTime, String endTime, String name, Integer indexNo, Integer rows) {
        String sql = " SELECT s.name cname,t.id,t.student_name,SUM(IF(r.type=0,1,0)) zccount,SUM(IF(r.type=1,1,0)) qqcount,SUM(IF(r.type=3,1,0)) " +
                " cdcount,SUM(IF(r.type=2,1,0)) qjcount FROM (class_rollcall c,student  t) LEFT JOIN  classes s ON (s.id=t.class_id) LEFT JOIN class_rollcall_student r ON (t.id=r.student_id " +
                " AND r.rollcall_id=c.id) WHERE c.class_id=t.class_id AND c.school_id=t.school_id AND c.school_id=? ";
        if (!StringUtils.isEmpty(gradeId)) {
            String sql1 = " SELECT GROUP_CONCAT(id)  string FROM classes WHERE grade_id=?";
            String s = attendJdbc.queryString(sql1, gradeId);
            if (!StringUtils.isEmpty(s)) {
                sql += " and c.class_id in (" + s + ")";
            }
        }

        if (!StringUtils.isEmpty(classId)) {
            sql += " and c.class_id =" + classId;
        }

        if (!StringUtils.isEmpty(startTime) && !StringUtils.isEmpty(endTime)) {
            sql += " AND c.add_time  between date_format('" + startTime + "','%Y-%m-%d') and date_format('" + endTime + "','%Y-%m-%d') ";
        } else {
            sql += " AND  TO_DAYS(now())=TO_DAYS(c.add_time) ";
        }

        if (!StringUtils.isEmpty(name)) {
            sql += " and t.student_name like '%" + name + "%'";
        }
        sql += " GROUP BY t.id";

        List<Map<String, Object>> list = attendJdbc.queryListMap(sql, schoolId);


        //创建分页工具
        PagingTool tool = new PagingTool();
        //放入页码
        tool.setIndexNo(indexNo);
        //放入每页显示数量
        tool.setRows(rows);
        //放入角色集合
        tool.setList(list);
        //返回数据
        return new Result(new PaginationUtil<>(list.size(), rows, tool.getList(), indexNo));
    }
}
